<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use think\Db;

/**
 * 
 *
 * @icon fa fa-circle-o
 */
class ConsultingHistory extends Backend
{
    
    /**
     * ConsultingHistory模型对象
     * @var \app\admin\model\ConsultingHistory
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\ConsultingCategory;

    }

    public function import()
    {
        parent::import();
    }

    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */
    /**
     * 查看
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            $consulting_category = config('database.prefix').'consulting_category';
            $consulting_history = config('database.prefix').'consulting_history';
            $h_createtime = $consulting_history.'.createtime';

            $limit = $this->request->get("limit/d", 999999);
            $filter = $this->request->get("filter", '');
            $filter = (array)json_decode($filter, true);
            $filter = $filter ? $filter : [];
            $where = [$consulting_category.'.deletetime'=>null];

            $where[$consulting_category.'.parent_id'] = 0;
            if ( $filter ) {

                foreach ($filter as $key => $value) {
                    if ( $key == $consulting_history.'.createtime' && $value ) {
                        $write_off_time = explode(' - ',$value);
                        $stat = strtotime($write_off_time[0]);
                        $end = strtotime($write_off_time[1]);
                        if ( $stat>0 && $end>0 ) {
                            $where[$h_createtime] = ['between',"$stat,$end"];
                        }
                    }
                }

            }

            $list = db('consulting_category')
                ->alias($consulting_category)
                ->where($where)
                ->field($consulting_category.'.*,count('.$consulting_history.'.id) as all_num')
                ->join('consulting_history '.$consulting_history,$consulting_history.'.consulting_category_id='.$consulting_category.'.id','LEFT')
                ->group($consulting_category.'.id')
                ->paginate($limit);

            $result = array("total" => $list->total(), "rows" => $list->items());

            return json($result);
        }
        return $this->view->fetch();
    }
    
    /**
     * [consulting_statistics 諮詢分類數據統計]
     * @MethodName
     * @Author     Allen
     * @DateTime   2021-10-28T00:58:59+0800
     * @param      string                   $value [description]
     * @return     [type]                          [description]
     */
    public function consulting_statistics($value='')
    {
        $array = file_get_contents('php://input');
        $array = json_decode($array,true);
        $stat = 0;
        $end = 0;

        $where = ['c.deletetime'=>null];
        $where['c.parent_id'] = 0;
        $consulting_history = config('database.prefix').'consulting_history';
        $h_createtime = $consulting_history.'.createtime';

        if ( $array ) {

            foreach ($array as $key => $value) {
                if ( $value['name'] == config('database.prefix').'consulting_history.createtime' && $value['value'] ) {
                    $write_off_time = explode(' - ',$value['value']);
                    $stat = strtotime($write_off_time[0]);
                    $end = strtotime($write_off_time[1]);
                    if ( $stat>0 && $end>0 ) {
                        $where[$h_createtime] = ['between',"$stat,$end"];
                    }
                }
            }

        }

        $title = '諮詢分類數據統計';

        $list = db('consulting_category')
                ->alias('c')
                ->field('c.*,count('.$consulting_history.'.id) as all_num')
                ->join('consulting_history '.$consulting_history,$consulting_history.'.consulting_category_id=c.id','LEFT')
                ->where($where)
                ->group('c.id')
                ->select();

        $list_buf = [];
        foreach ($list as $key => $value) {
            $all_num = $value['all_num'];

            $list_buf[] = ['index_date'=>$value['name'],'all_num'=>$all_num];
        }

        $index_date = [];
        $all_num = [];
        foreach ($list_buf as $key => $value) {
            $index_date[] = $value['index_date'];
            $all_num[] = $value['all_num'];
        }

        $result = array("index_date" => $index_date, "all_num" => $all_num,'title'=>$title,'legend'=>'諮詢次數','unit'=>'次');

        return json($result);
    }

    /**
     * [integral_recharge_index 積分充值金額頁面]
     * @MethodName
     * @Author     Allen
     * @DateTime   2021-10-28T12:20:30+0800
     * @param      string                   $value [description]
     * @return     [type]                          [description]
     */
    public function integral_recharge_index($value='')
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            $stat = strtotime(date('Y-m-d',strtotime('-6 day')));
            $end = strtotime(date('Y-m-d',time()))+86399;
            $paytime = input('filter');
            $paytime = (array)json_decode($paytime, true);
            if ( $paytime ) {
                $paytime = explode(' - ',$paytime['paytime']);
                $stat = strtotime($paytime[0]);
                $end = strtotime($paytime[1]);
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $prefix = config('database.prefix');
            $sql = "SELECT COUNT(*) as num, FROM_UNIXTIME(paytime,'%Y-%m-%d') as gap from  $prefix"."recharge ";
            // $sql .= " where seller_id = $seller_id ";

            if ( $stat>0 && $end>0 ) {
                $sql .= " where paytime>=$stat and paytime<=$end ";
            }else{
                $sql .= " where paytime>0";
            }

            $sql .= " group by gap ";

            $res = DB::query($sql);//订单数,交易额
            if ( !$stat && $res ) {
                $stat = strtotime($res[0]['gap']);
            }
            if ( !$end && $res ) {
                // $end = strtotime($res[count($res)-1]['gap']);
                $end = strtotime(date('Y-m-d',time()))+86399;
            }

            if ( !$stat && !$end ) {
                $stat = strtotime(date('Y-m-d',strtotime('-6 day')));
                $end = strtotime(date('Y-m-d',time()))+86399;
            }

            $list = [];
            for($i=$stat;$i<=$end;$i=$i+24*3600){
                $date = date('Y-m-d',$i);
                $list[] = array('day'=>$date,'day_strtotime'=>strtotime($date));
            }

            foreach ($list as $key => &$value) {
                $value['payment_amount'] = db('recharge')->where(['paytime'=>['between',[$value['day_strtotime'],$value['day_strtotime']+86399]]])->sum('payment_amount');

            }
            unset($value);
            rsort($list);

            $result = array("total" => 0, "rows" => $list);

            return json($result);
        }

        return $this->view->fetch();
    }

    /**
     * [integral_recharge_statistics 積分充值金額統計圖]
     * @MethodName
     * @Author     Allen
     * @DateTime   2021-10-28T12:24:41+0800
     * @return     [type]                   [description]
     */
    public function integral_recharge_statistics(){
        // $seller_id = $this->seller_admin_id;
        $array = file_get_contents('php://input');
        $array = json_decode($array,true);
        $stat = strtotime(date('Y-m-d',strtotime('-6 day')));
        $end = strtotime(date('Y-m-d',time()))+86399;

        if ( $array ) {

            foreach ($array as $key => $value) {
                if ( $value['name'] == 'paytime' && $value['value'] ) {
                    $paytime = explode(' - ',$value['value']);
                    $stat = strtotime($paytime[0]);
                    $end = strtotime($paytime[1]);
                }
            }

        }

        $prefix = config('database.prefix');
        $sql = "SELECT COUNT(*) as num,sum(payment_amount) as pay_price, FROM_UNIXTIME(paytime,'%Y-%m-%d') as gap from  $prefix"."recharge ";
        // $sql .= " where seller_id=$seller_id ";

        if ( $stat>0 && $end>0 ) {
            $sql .= " where paytime>=$stat and paytime<=$end ";
        }else{
            $sql .= " where paytime>0 ";
        }

        $sql .= " group by gap ";
        $res = DB::query($sql);//订单数,交易额

        $list = [];
        for($i=$stat;$i<=$end;$i=$i+24*3600){
            $date = date('Y-m-d',$i);
            $list[] = array('day'=>$date,'day_strtotime'=>strtotime($date));
        }

        foreach ($list as $key => &$value) {
            // 收入
            $value['payment_amount'] = db('recharge')->where(['pay_status'=>'20','paytime'=>['between',[$value['day_strtotime'],$value['day_strtotime']+86399]]])->sum('payment_amount');

        }
        unset($value);
        ksort($list);

        $index_date = [];
        $payment_amount = [];
        foreach ($list as $val){
            $index_date[] = $val['day'];
            $payment_amount[] = $val['payment_amount'];
        }

        $result = array("index_date" => $index_date, "all_num" => $payment_amount,'title'=>'積分充值金額','legend'=>'金額','unit'=>'HK$');

        return json($result);
    }

    /**
     * [withdrawal_index 提現金額頁面]
     * @MethodName
     * @Author     Allen
     * @DateTime   2021-10-28T12:20:30+0800
     * @param      string                   $value [description]
     * @return     [type]                          [description]
     */
    public function withdrawal_index($value='')
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            $stat = strtotime(date('Y-m-d',strtotime('-6 day')));
            $end = strtotime(date('Y-m-d',time()))+86399;
            $createtime = input('filter');
            $createtime = (array)json_decode($createtime, true);
            if ( $createtime ) {
                $createtime = explode(' - ',$createtime['createtime']);
                $stat = strtotime($createtime[0]);
                $end = strtotime($createtime[1]);
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $prefix = config('database.prefix');
            $sql = "SELECT COUNT(*) as num, FROM_UNIXTIME(createtime,'%Y-%m-%d') as gap from  $prefix"."shop_withdrawals ";

            if ( $stat>0 && $end>0 ) {
                $sql .= " where createtime>=$stat and createtime<=$end ";
            }else{
                $sql .= " where createtime>0";
            }

            $sql .= ' and status = 1 ';

            $sql .= " group by gap ";

            $res = DB::query($sql);//订单数,交易额
            if ( !$stat && $res ) {
                $stat = strtotime($res[0]['gap']);
            }
            if ( !$end && $res ) {
                // $end = strtotime($res[count($res)-1]['gap']);
                $end = strtotime(date('Y-m-d',time()))+86399;
            }

            if ( !$stat && !$end ) {
                $stat = strtotime(date('Y-m-d',strtotime('-6 day')));
                $end = strtotime(date('Y-m-d',time()))+86399;
            }

            $list = [];
            for($i=$stat;$i<=$end;$i=$i+24*3600){
                $date = date('Y-m-d',$i);
                $list[] = array('day'=>$date,'day_strtotime'=>strtotime($date));
            }

            foreach ($list as $key => &$value) {
                $value['money'] = db('shop_withdrawals')->where(['createtime'=>['between',[$value['day_strtotime'],$value['day_strtotime']+86399]],'status'=>'1'])->sum('money');

            }
            unset($value);
            rsort($list);

            $result = array("total" => 0, "rows" => $list);

            return json($result);
        }

        return $this->view->fetch();
    }

    /**
     * [withdrawal_statistics 提現金額統計圖]
     * @MethodName
     * @Author     Allen
     * @DateTime   2021-10-28T12:24:41+0800
     * @return     [type]                   [description]
     */
    public function withdrawal_statistics(){
        // $seller_id = $this->seller_admin_id;
        $array = file_get_contents('php://input');
        $array = json_decode($array,true);
        $stat = strtotime(date('Y-m-d',strtotime('-6 day')));
        $end = strtotime(date('Y-m-d',time()))+86399;

        if ( $array ) {

            foreach ($array as $key => $value) {
                if ( $value['name'] == 'createtime' && $value['value'] ) {
                    $createtime = explode(' - ',$value['value']);
                    $stat = strtotime($createtime[0]);
                    $end = strtotime($createtime[1]);
                }
            }

        }

        $prefix = config('database.prefix');
        $sql = "SELECT COUNT(*) as num,sum(money) as pay_price, FROM_UNIXTIME(createtime,'%Y-%m-%d') as gap from  $prefix"."shop_withdrawals ";
        // $sql .= " where seller_id=$seller_id ";

        if ( $stat>0 && $end>0 ) {
            $sql .= " where createtime>=$stat and createtime<=$end ";
        }else{
            $sql .= " where createtime>0 ";
        }

        $sql .= ' and status = 1 ';

        $sql .= " group by gap ";
        $res = DB::query($sql);//订单数,交易额

        $list = [];
        for($i=$stat;$i<=$end;$i=$i+24*3600){
            $date = date('Y-m-d',$i);
            $list[] = array('day'=>$date,'day_strtotime'=>strtotime($date));
        }

        foreach ($list as $key => &$value) {
            // 收入
            $value['money'] = db('shop_withdrawals')->where(['status'=>'1','createtime'=>['between',[$value['day_strtotime'],$value['day_strtotime']+86399]]])->sum('money');

        }
        unset($value);
        ksort($list);

        $index_date = [];
        $money = [];
        foreach ($list as $val){
            $index_date[] = $val['day'];
            $money[] = $val['money'];
        }

        $result = array("index_date" => $index_date, "all_num" => $money,'title'=>'提現金額','legend'=>'金額','unit'=>'HK$');

        return json($result);
    }


    /**
     * [transaction_index 交易金額頁面]
     * @MethodName
     * @Author     Allen
     * @DateTime   2021-10-28T12:20:30+0800
     * @param      string                   $value [description]
     * @return     [type]                          [description]
     */
    public function transaction_index($value='')
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            $stat = strtotime(date('Y-m-d',strtotime('-6 day')));
            $end = strtotime(date('Y-m-d',time()))+86399;
            $paytime = input('filter');
            $paytime = (array)json_decode($paytime, true);
            if ( $paytime ) {
                $paytime = explode(' - ',$paytime['paytime']);
                $stat = strtotime($paytime[0]);
                $end = strtotime($paytime[1]);
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $prefix = config('database.prefix');
            $sql = "SELECT COUNT(*) as num, FROM_UNIXTIME(paytime,'%Y-%m-%d') as gap from  $prefix"."order ";

            if ( $stat>0 && $end>0 ) {
                $sql .= " where paytime>=$stat and paytime<=$end ";
            }else{
                $sql .= " where paytime>0";
            }

            $sql .= ' and order_status in(20,30,40) ';

            $sql .= " group by gap ";

            $res = DB::query($sql);//订单数,交易额
            if ( !$stat && $res ) {
                $stat = strtotime($res[0]['gap']);
            }
            if ( !$end && $res ) {
                // $end = strtotime($res[count($res)-1]['gap']);
                $end = strtotime(date('Y-m-d',time()))+86399;
            }

            if ( !$stat && !$end ) {
                $stat = strtotime(date('Y-m-d',strtotime('-6 day')));
                $end = strtotime(date('Y-m-d',time()))+86399;
            }

            $list = [];
            for($i=$stat;$i<=$end;$i=$i+24*3600){
                $date = date('Y-m-d',$i);
                $list[] = array('day'=>$date,'day_strtotime'=>strtotime($date));
            }

            foreach ($list as $key => &$value) {
                $value['money'] = db('order')->where(['paytime'=>['between',[$value['day_strtotime'],$value['day_strtotime']+86399]],'order_status'=>['in',['20','30','40']]])->sum('pay_price');

            }
            unset($value);
            rsort($list);

            $result = array("total" => 0, "rows" => $list);

            return json($result);
        }

        return $this->view->fetch();
    }

    /**
     * [withdrawal_statistics 提現金額統計圖]
     * @MethodName
     * @Author     Allen
     * @DateTime   2021-10-28T12:24:41+0800
     * @return     [type]                   [description]
     */
    public function transaction_statistics(){
        // $seller_id = $this->seller_admin_id;
        $array = file_get_contents('php://input');
        $array = json_decode($array,true);
        $stat = strtotime(date('Y-m-d',strtotime('-6 day')));
        $end = strtotime(date('Y-m-d',time()))+86399;

        if ( $array ) {

            foreach ($array as $key => $value) {
                if ( $value['name'] == 'paytime' && $value['value'] ) {
                    $paytime = explode(' - ',$value['value']);
                    $stat = strtotime($paytime[0]);
                    $end = strtotime($paytime[1]);
                }
            }

        }

        $prefix = config('database.prefix');
        $sql = "SELECT COUNT(*) as num,sum(pay_price) as pay_price, FROM_UNIXTIME(paytime,'%Y-%m-%d') as gap from  $prefix"."order ";
        // $sql .= " where seller_id=$seller_id ";

        if ( $stat>0 && $end>0 ) {
            $sql .= " where paytime>=$stat and paytime<=$end ";
        }else{
            $sql .= " where paytime>0 ";
        }

        $sql .= ' and order_status in(20,30,40) ';

        $sql .= " group by gap ";
        $res = DB::query($sql);//订单数,交易额

        $list = [];
        for($i=$stat;$i<=$end;$i=$i+24*3600){
            $date = date('Y-m-d',$i);
            $list[] = array('day'=>$date,'day_strtotime'=>strtotime($date));
        }

        foreach ($list as $key => &$value) {
            // 收入
            $value['money'] = db('order')->where(['order_status'=>['in',['20','30','40']],'paytime'=>['between',[$value['day_strtotime'],$value['day_strtotime']+86399]]])->sum('pay_price');

        }
        unset($value);
        ksort($list);

        $index_date = [];
        $money = [];
        foreach ($list as $val){
            $index_date[] = $val['day'];
            $money[] = $val['money'];
        }

        $result = array("index_date" => $index_date, "all_num" => $money,'title'=>'交易訂單金額','legend'=>'金額','unit'=>'HK$');

        return json($result);
    }

    /**
     * 資訊訪問統計頁面
     */
    public function information_access_index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }

            $limit = $this->request->get("limit/d", 999999);
            $filter = $this->request->get("filter", '');
            $filter = (array)json_decode($filter, true);
            $filter = $filter ? $filter : [];

            $where = [];
            if ( $filter ) {

                foreach ($filter as $key => $value) {
                    if ( $key == 'createtime' && $value ) {
                        $write_off_time = explode(' - ',$value);
                        $stat = strtotime($write_off_time[0]);
                        $end = strtotime($write_off_time[1]);
                        if ( $stat>0 && $end>0 ) {
                            $where['createtime'] = ['between',"$stat,$end"];
                        }
                    }
                }

            }

            $list = db('article_history')
                ->where($where)
                ->field('type,count(id) as all_num')
                ->group('type')
                ->select();

            $types = db('article_history')->where($where)->group('type')->column('type');
            $types_arr = [1,2,3,4,5];
            foreach ($types_arr as $key => $value) {
                if ( !in_array($value, $types) ) {
                    $list[] = ['type'=>$value,'all_num'=>0];
                }
            }

            $type_arr = ['1'=>'大灣區資訊','2'=>'大灣區調查','3'=>'工聯中心','4'=>'活動','5'=>'互助廣場'];
            foreach ($list as $key => &$value) {
                $value['name'] = $type_arr[$value['type']];
            }
            unset($value);
            $result = array("total" => 0, "rows" => $list);

            return json($result);
        }
        $this->assignconfig("access_user_list", $this->auth->check("consulting_history/access_user_list"));
        return $this->view->fetch();
    }
    
    /**
     * [information_access_statistics 資訊訪問數據統計]
     * @MethodName
     * @Author     Allen
     * @DateTime   2021-10-28T00:58:59+0800
     * @param      string                   $value [description]
     * @return     [type]                          [description]
     */
    public function information_access_statistics($value='')
    {
        $array = file_get_contents('php://input');
        $array = json_decode($array,true);
        $stat = 0;
        $end = 0;

        $where = [];
        if ( $array ) {

            foreach ($array as $key => $value) {
                if ( $value['name'] == 'createtime' && $value['value'] ) {
                    $write_off_time = explode(' - ',$value['value']);
                    $stat = strtotime($write_off_time[0]);
                    $end = strtotime($write_off_time[1]);
                    if ( $stat>0 && $end>0 ) {
                        $where['createtime'] = ['between',"$stat,$end"];
                    }
                }
            }

        }


        $list = db('article_history')
                ->field('type,count(id) as all_num')
                ->where($where)
                ->group('type')
                ->select();
            $types = db('article_history')->where($where)->group('type')->column('type');
            $types_arr = [1,2,3,4,5];
            foreach ($types_arr as $key => $value) {
                if ( !in_array($value, $types) ) {
                    $list[] = ['type'=>$value,'all_num'=>0];
                }
            }

        $list_buf = [];
        $type_arr = ['1'=>'大灣區資訊','2'=>'大灣區調查','3'=>'工聯中心','4'=>'活動','5'=>'互助廣場'];
        foreach ($list as $key => $value) {
            $all_num = $value['all_num'];

            $list_buf[] = ['index_date'=>$type_arr[$value['type']],'all_num'=>$all_num];
        }

        $index_date = [];
        $all_num = [];
        foreach ($list_buf as $key => $value) {
            $index_date[] = $value['index_date'];
            $all_num[] = $value['all_num'];
        }

        $result = array("index_date" => $index_date, "all_num" => $all_num,'title'=>'資訊訪問統計','legend'=>'訪問次數','unit'=>'次');

        return json($result);
    }
    /**
     * 資訊訪問詳情
     */
    public function access_user_list($ids = null,$type = null)
    {
        $this->assignconfig("ids", $ids);
        $this->assignconfig("type", $type);
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            $user = config('database.prefix').'user';
            $article_history = config('database.prefix').'article_history';

            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $field = $user.'.id,'.$user.'.avatar,'.$user.'.nickname,'.$user.'.mobile,'.$user.'.province,'.$user.'.city,'.$user.'.district,'.$article_history.'.createtime';
            $where_buf = [$article_history.'.type'=>$type];

            $total = db('article_history')
                ->alias($article_history)
                ->where($where_buf)
                ->join('user '.$user,$article_history.'.user_id='.$user.'.id','LEFT')
                ->where($where)
                ->count();

            $list = db('article_history')
                ->field($field)
                ->alias($article_history)
                ->where($where_buf)
                ->join('user '.$user,$article_history.'.user_id='.$user.'.id','LEFT')
                ->where($where)
                ->order($article_history.'.createtime desc')
                ->limit($offset, $limit)
                ->select();
            foreach ($list as $k => &$v) {
                $v['avatar'] = $v['avatar'] ? cdnurl($v['avatar'], true) : letter_avatar($v['nickname']);
                $region_address = db('region')->where(['id'=>$v['province']])->value('name');
                $city = db('region')->where(['id'=>$v['city']])->value('name');
                if ( $city ) {
                    $region_address = $region_address.$city;
                    $district = db('region')->where(['id'=>$v['district']])->value('name');
                    if ( $district ) {
                        $region_address = $region_address.$district;
                    }
                }
                $v['region_address'] = $region_address?$region_address:'';

            }
            unset($v);
            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }
    

}
